/*
 * Copyright (c) 2012 Aleksander Zotov, Benedikt Markt, Aliaksei Babkovich
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 *  of this software and associated documentation files (the "Software"),
 *  to deal in the Software without restriction, including without limitation
 *  the rights to use, copy, modify, merge, publish, distribute, sublicense,
 *  and/or sell copies of the Software, and to permit persons to whom the
 *  Software is furnished to do so, subject to the following conditions:
 *  
 *  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 *  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 *  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
 *  IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
 *  DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR
 *  OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR
 *  THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */

package database;

//import java.util.ResourceBundle;
//import gui.MessageController;

import java.sql.*;

import simulation.Simulation;
import user.User;
import user.UserManager;
import utils.ApplicationPreferences;

/**
 * This class holds all methods to store and delete simulations and user from corresponding database tables
 * 
 * @author Aliaksei Babkovich
 * @see UserManager
 * @see Simulations
 * @see SimulationManager
 * @ TODO remove UserPreferences class from draft
 */

public class DBManager {
	
	String url = "jdbc:mysql://localhost:3306/";
	String dbName = "test";
	String driver = "com.mysql.jdbc.Driver";
	String username = ""; 
	String password = "root";
	String databaseIP;
	
	public DBManager() {
		ApplicationPreferences prefs = ApplicationPreferences.getInstance();
		databaseIP = prefs.getDbURL();
	}
	/**
	 * Create table in DB for Simulations
	 */
	private void CreateSimTable(){
		Connection createSimTableConnection = null;
		try {
			createSimTableConnection = getConnection();
			Statement createSimTableStatement = createSimTableConnection.createStatement();
			
			/*Query for creating MySQL table Simulations with columns : name, description, collisionDistance, referenceSystem, startTime,
			endTime, identifier, timeStamp. Primary key - identifier.*/
			String SIMULATION_TABLE ="CREATE TABLE Simulations (" + " name VARCHAR (50), description VARCHAR (50),"
					+ " collisionDistance FLOAT, referenceSystem VARCHAR (50), startTime DATETIME, endTime DATETIME," 
					+ " username VARCHAR (20), collisionCount INTEGER, refData VARCHAR (50)," 
					+ " identifier VARCHAR (10) PRIMARY KEY, timeStamp DATETIME)";
			createSimTableStatement.executeUpdate(SIMULATION_TABLE);
			createSimTableConnection.close();
		}catch (SQLException e) {
			System.out.println("Table is allready exists!");
		}catch (Exception e) {
			System.out.println("Failed to connect to Database");
			e.printStackTrace();
		}
	}
			 
	
	/**
	 * Create table in DB for Users
	 */
	private void CreateUserTable () {
		Connection createUserTableConnection = null;
		try {
			createUserTableConnection = getConnection();
			Statement createUserTableStatement = createUserTableConnection.createStatement();
			
			//Query for creating MySQL table Users with columns: name, password, isAdmin, language, referenceSystem 
			String USER_TABLE ="CREATE TABLE Users (" + " name VARCHAR (20) PRIMARY KEY, password VARCHAR (20),"
					+ " isAdmin BOOL, language VARCHAR (30), referenceSystem VARCHAR (50))";
			createUserTableStatement.executeUpdate(USER_TABLE);
			createUserTableConnection.close();
		}catch (SQLException e) {
			System.out.println("Table is allready exists!");
		}catch (Exception e) {
			System.out.println("Failed to connect to Database");
			e.printStackTrace();
		}
	}
	
	
	/**
	 * Saving Simulation in DB
	 * 
	 * @param simulation
	 */
	public void storeSimulation(Simulation simulation) {
		CreateSimTable();
		Connection storeSimConnection = null;
		PreparedStatement insertSimStatement = null;
 		try {
 			storeSimConnection = getConnection();
 			String insertQuery = ("insert into Simulations(name, description, collisionDistance, referenceSystem, startTime,"
 					+ " endTime, username, collisionCount, refData, identifier, timeStamp)" 
 					+ " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
 			insertSimStatement = storeSimConnection.prepareStatement(insertQuery);
 			
 			//Assigning values to DB fields
 			insertSimStatement.setString	(1, simulation.getName());
 			insertSimStatement.setString	(2, simulation.getDescription());
 			insertSimStatement.setFloat		(3, simulation.getCollisionDistance());
 			insertSimStatement.setString	(4, simulation.getReferenceSystem());
 			insertSimStatement.setDate		(5, (java.sql.Date) simulation.getStartTime());
 			insertSimStatement.setDate		(6, (java.sql.Date) simulation.getEndTime());
 			insertSimStatement.setString	(7, simulation.getUsername());
 			insertSimStatement.setInt		(8, simulation.getCollisionCount());
 			insertSimStatement.setString	(9, simulation.getRefData().toString());
 			insertSimStatement.setString	(10, simulation.getIdentifier());
 			insertSimStatement.setDate		(11, (java.sql.Date) simulation.getTimeStamp());
 			
 			insertSimStatement.executeUpdate();
 			storeSimConnection.close();
 			} catch (ClassNotFoundException e) {
		      System.out.println("error: failed to load MySQL driver.");
		      e.printStackTrace();
 			} catch (SQLException e) {
		      System.out.println("error: failed to create a connection object.");
		      e.printStackTrace();
		    } catch (Exception e) {
		      System.out.println("other error:");
		      e.printStackTrace();
		      }
	}

	
	/**
	 * Delete specific simulation from the Simulations table
	 * 
	 * @param identifier
	 */
	public void deleteSimulation(String identifier) {
		Connection deleteSimConnection = null;
		
		try{
			deleteSimConnection = DriverManager.getConnection(url, username, password);
			try{
				String sqlStatment = "DELETE FROM Simulations WHERE identifier = ?";
				PreparedStatement deleteSimStatement = deleteSimConnection.prepareStatement(sqlStatment);
				
				//Determine per identifier which simulation will be deleted
				deleteSimStatement.setString(1, identifier);
				
				deleteSimStatement.executeUpdate();
				deleteSimConnection.close();
			} catch (SQLException s) {
				System.out.println("SQL statement is not executed!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * Adds a new user, if it doesn't exist yet, or updates user data if user
	 * exists
	 * 
	 * @param user
	 */
	public void storeUser(User user) {
		CreateUserTable ();
		//MessageController messageController = new MessageController();
		Connection storeUserConnection = null;
		PreparedStatement insertUserPrepStatement = null;
		PreparedStatement updateUserPrepStatement = null;
		
 		try{
 			storeUserConnection = getConnection();
 			Statement checkUserStatement = storeUserConnection.createStatement();
 			ResultSet checkUserResultSet = checkUserStatement.executeQuery("SELECT name FROM Users WHERE name LIKE '"+'%'+ user.getName() +'%'+"'");
 			if (checkUserResultSet.next()){
 				try{
 					String updateQuery = ("UPDATE Users SET password ='" + user.getPassword() + "', isAdmin= '" + user.isAdmin() + 
 	 						"', language= '" + user.getLanguage() + "', referenceSystem= '" + user.getReferenceSystem());
 	 				updateUserPrepStatement = storeUserConnection.prepareStatement(updateQuery);
 	 				
 	 				updateUserPrepStatement.executeUpdate();
 	 				//log.writeError("Benutzerdaten wurden geändert!");
 				}catch (SQLException e){
 					System.out.println("SQL statement is not executed!");
 				    e.printStackTrace();
 				}
 			}else{
 				try{
 					String insertQuery = ("insert into Users(name, password, isAdmin, language, referenceSystem)" 
 	 	 					+ " values (?, DES_ENCRYPT(?), ?, ?, ?) ");
 	 				insertUserPrepStatement = storeUserConnection.prepareStatement(insertQuery);
 	 				
 	 				//Assigning values to DB fields
 	 	 			insertUserPrepStatement.setString	(1, user.getName());
 	 	 			insertUserPrepStatement.setString	(2, user.getPassword());
 	 	 			insertUserPrepStatement.setBoolean	(3, user.isAdmin());
 	 	 			insertUserPrepStatement.setString	(4, user.getLanguage().toString());
 	 	 			insertUserPrepStatement.setString	(5, user.getReferenceSystem());
 	 	 			
 	 	 			insertUserPrepStatement.execute();
 	 	 		//log.writeError("Benutzerdaten wurden geändert!");
 				}catch (SQLException e){
 					System.out.println("Neuen Benutzer wurde gespeichert");
 				    e.printStackTrace();
 				}
 			}
 				
 			storeUserConnection.close();
 		} catch (ClassNotFoundException e) {
		    System.out.println("error: failed to load MySQL driver.");
		    e.printStackTrace();
 		} catch (SQLException e) {
		    System.out.println("error: failed to create a connection object.");
		    e.printStackTrace();
	    } catch (Exception e) {
	    	System.out.println("other error:");
		    e.printStackTrace();
		}
	}

	
	/**
	 * Delete specific user from table Users
	 * 
	 * @param user
	 */
	public void deleteUser(User user) {
		Connection deleteUserConnection = null;
		
		try{
			deleteUserConnection = DriverManager.getConnection(url+dbName, username, password);
			try{
				String sqlStatment = "DELETE FROM Users WHERE name = ?";
				PreparedStatement deleteUserStatement = deleteUserConnection.prepareStatement(sqlStatment);
				
				//Determine per name which user will be deleted
				deleteUserStatement.setString(1, user.getName());
				
				deleteUserStatement.executeUpdate();
				deleteUserConnection.close();
			} catch (SQLException s) {
				System.out.println("SQL statement is not executed!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Checks if the database server is available
	 * 
	 * @return
	 */
	public boolean isAvailable() {
		System.out.println("NIY: DatabaseManager.isAvailable");
		return false;
	}
	
	/**
	 * Connect to Database
	 * 
	 * @return
	 */
	public Connection getConnection() throws Exception {
	    Class.forName(driver);
	    Connection connection = DriverManager.getConnection(url+dbName, username, password);
	    return connection;
	  }
	
//	public Connection Connect(String DatabaseProperties) throws ClassNotFoundException, SQLException {
//	ResourceBundle rb = ResourceBundle.getBundle(DatabaseProperties);
//	String username = rb.getString("connections.jdbc.username");
//	String password = rb.getString("connections.jdbc.password");
//	String driver = rb.getString("connections.jdbc.username");
//	String url = rb.getString("connections.jdbc.url");
//	String dbName = rb.getString("connections.jdbc.dbName");
//	Class.forName(rb.getString("connections.jdbc.driver"));
//	}	
	
}
